Opdracht Databanken normaliseren

Doelstelling

Bij het inserten in een genormaiseerde database moet je rekening houden met primaire- en vreemde sleutels die de tabellen onderling met elkaar verbinden.

Een ERD voorstelling van onze database:

Oefeningen

Een boek en de auteur inserten in de 'genormaliseerde' database

Eerste oefening

Als eerste voorbeeld nemen we een boek geschreven door Hilary Mantel. De gegevens halen we van de website van De Standaard Boekhandel.

We hebben nu drie tabellen. We beginnen met het inserten van de persoonsgevens:

-- ji
-- 8 januari 2013
--
use ModernWays;
-- we gaan eerst een boek van een vrouw toevoegen
-- bestandnaam: BoekenNormalizeInsertOne.sql
--
-- auteur toevoegen
-- de Id van Mevrouw is 1, dat is de Id kolom van de Aanspreektitel tabel
insert into Personen (
   Voornaam, 
   Familienaam,
   IdAanspreekTitel
)
values (
   'Hilary', 
   'Mantel',
   1
);

select * from Personen:

Onthoud de primary key waarde van Hilary Mantel. In ons voorbeeld is dat 17. Vervolgens voegen we het boek toe:

insert into Boeken (
   Titel,
   Stad, 
   Uitgeverij, 
   Verschijningsjaar,
   Herdruk, 
   Commentaar, 
   Categorie, 
   IdAuteur,
   InsertedBy
)
values
(
   'Wolf Hall', 
   '',
   'Fourth Estate; First Picador Edition First Printing edition',
   '2010', 
   '', 
   'Goed boek', 
   'Thriller', 
   17,
   'JI'
);

select * from Boeken;

De relatie tussen het boek en de persoon die het boek geschreven heeft wordt bepaald door foreign key IdAuteur in de tabel Boeken. De waarde 16 in die kolom verwijst naar een waarde in de primary key Id van de tabel Personen.

Tweede oefening

Voeg het volgende boek toe:

Jean-Paul Sartre, De Woorden, 1961, De Bezige Bij.

We beginnen met de auteur toe te voegen indien die nog niet is toegevoegd:

-- ji
-- 8 januari 2018
--
use ModernWays;
-- we gaan eerst een boek van een vrouw toevoegen
-- bestandnaam: BoekenNormalizeInsertHilaryMantelOne.sql
--
-- auteur toevoegen
-- de Id van Meneer is 2, dat is de Id kolom van de Aanspreektitel tabel
insert into Personen (
   Voornaam,
   Familienaam,
   IdAanspreekTitel,
   Stad
)
values (
   'Jean-Paul',
   'Sartre',
   2,
   'Parijs'
);

Vul de waarde in de kolom IdAuteur niet letterlijk in maar door middel van een SQL statement. M.a.w. hoe kan je de waarde die in de kolom IdAuteur moet komen opvragen?

We gebruiken hiervoor een subquery:

-- bestandnaam: BoekenNormalizeInsertSartreOne.sql
use ModernWays;
insert into Boeken (
   Titel,
   Stad,
   Verschijningsjaar,
   Commentaar,
   Categorie,
   IdAuteur,
   InsertedBy
)

values (
   'De Woorden',
   'Antwerpen',
   '1962',
   'Een zeer mooi boek.',
   'Roman',
   (select Id from Personen where
      Familienaam = 'Sartre' and Voornaam = 'Jean-Paul'),
   'JI')

Let erop dat de Id van de auteur in de tabel Boeken opgehaald uit de tabel Personen met behulp van een subquerie.

Derde oefening

We voegen een boek van Hegel toe. We beginnen met de persoon toe te voegen:

-- ji
-- 8 januari 2018
--
use ModernWays;
-- we gaan eerst een boek van een vrouw toevoegen
-- bestandnaam: BoekenNormalizeInsertHilaryMantelOne.sql
--
-- auteur toevoegen
-- de Id van Meneer is 2, dat is de Id kolom van de Aanspreektitel tabel
insert into Personen (
   Voornaam,
   Familienaam,
   IdAanspreekTitel,
   Stad
)
values (
   'Georg Wilhelm',
   'Hegel',
   2,
   'Berlijn'
)

Vervolgens voegen we een werk van Hegel toe:

use ModernWays;
insert into Boeken (
   Titel,
   Verschijningsjaar,
   Uitgeverij,
   Stad,
   Categorie,
   InsertedBy,
   Commentaar,
   IdAuteur
)
values (
   'De fenomenologie van de geest',
   '2013',
   'Boom Uitgevers',
   'Amsterdam',
   'Filosofie',
   'JI',
   'Een van de mooiste filosofische boeken aller tijden.',
   (select top 1 Id from Personen where Familienaam='Hegel')
);

Opdrachten

1. Schrijf een sql script waarmee je 3 boeken van Hilary Mantel kan toevoegen (genormaliseerd) en sla het script op in het bestand met de naam BoekenInsertHilaryMantel.sql. Het gebruik de gegevens van de Standaard Boekhandel website.
2. Schrijf een script waarmee je 3 boeken van David Hume aan de genormaliseerde database toevoegt (genormaliseerd). Sla beiden scripts op in een bestand met de naam BoekenInsertDavidHume.sql.
3. Delete de kolom Aanspreektitel uit de tabel Personen. Sla dit script op in het bestand met de naam PersonenAlterAanspreektitelDrop.sql.
4. Select scripts: voor elk van onderstaande selects toon je de Titel, het Verschijningsjaar, wie het boek heeft geïnserted, de Voornaam, de Familienaam en de Aanspreektitel van de auteur. Sla de select scripts op in een bestand met de naam BoekenSelectOpdrachten.sql:
   1. Schrijf een script waarmee je alle boeken selecteert die door David Hume en Hilary Mantel zijn geschreven.
   2. Schrijf een script waarmee je alle boeken selecteert die door David Hume en Hilary Mantel zijn geschreven en orden ze op de Titel.
   3. Schrijf een script waarmee je alle boeken selecteert die door Hilary Mantel zijn geschreven tussen 2005 en 2007, orden ze op Verschijningsjaar.
   4. Schrijf een script waarmee je alle boeken selecteert die tussen 1900 en 1999 zijn geschreven, order ze op Familienaam.
   5. Schrijf een script waarmee je alle boeken selecteert waarvan de familienaam van de auteur begint met de letter 'T'.
   6. Schrijf een script waarmee je alle boeken selecteert waarvan de titel het woord 'economie' bevat en orden ze op Titel in alfabetisch dalende orde.
   7. Schrijf een script waarmee je alle boeken toont die door een vrouw zijn geschreven. Orden ze op Verschijningsjaar en dan op Familienaam en Voornaam.
   8. Schrijf een script die alle personen toont die geen boeken hebben geschreven.
   9. Schrijf een script waarmee je alle boeken van mannelijke schrijvers toont die na 2000 gepubliceerd hebben, in alfabetische orde op Familienaam en Voornaam.
   10. Schrijf een script die alle boeken toont die door Hegel en Sartre zijn geschreven. Gebruik de IN operator.